﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Restaurant.DAO
{
    public class BanDB
    {
        private SqlConnection con = KetNoiDB.TaoKetNoi();
        
        public DataTable LoadBan()
        {
            con.Open();
            string strcmd = "dbo.spLoadBan";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable LoadLoaiBan()
        {
            con.Open();
            string strcmd = "dbo.spLoadLoaiBan";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable LayMaLoaiBan(string tenLoaiBan)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spLayMaLoaiBan";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@tenLoaiBan", tenLoaiBan));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public bool XoaBan(int ma)
        {

            try
            {
                con.Open();
                string strcmd = "dbo.spXoaBan";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ma", ma));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool ThemBan(string tenBan, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spThemBan";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@tenBan", tenBan));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable TimBan(int maBan)
        {
            con.Open();
            string strcmd = "dbo.spTimBan";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@maBan",maBan));
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable TimBanTheoNH(int maNH)
        {
            con.Open();
            string strcmd = "dbo.spTimBanTheoNH";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ma", maNH));
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable TimBanTheoKhuNH(int maNH, int makhu)
        {
            con.Open();
            string strcmd = "dbo.spTimBanKhuNH";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@maNH", maNH));
            cmd.Parameters.Add(new SqlParameter("@maKhu", makhu));
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable TimBanTheoKhuNHLoai(int maNH, int maKhu, int maLoaiBan)
        {
            con.Open();
            string strcmd = "dbo.spTimBanKhuNHLoai";
            SqlCommand cmd = new SqlCommand(strcmd, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@maNH", maNH));
            cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
            cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable dbtb = new DataTable();
            adapt.Fill(dbtb);
            con.Close();
            return dbtb;
        }

        public DataTable TimBanTheoKhuNHLoaiTinhTrang(int maNH, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spTimBanKhuNHLoaiTinhTrang";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNH", maNH));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CapNhapBan(int maBan, string tenBan, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spSuaBan";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maBan", maBan));
                cmd.Parameters.Add(new SqlParameter("@tenBan", tenBan));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //==========
        public DataTable LoadBanDirtyreadError(int maNH, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spDirtyRead_TimBan_Error";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNH", maNH));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LoadBanDirtyread(int maNH, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spDirtyRead_TimBan_Fixed";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maNH", maNH));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return dbtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CapNhapBanDirtyreadError(int maBan, string tenBan, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spDirtyRead_SuaBan_Error";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maBan", maBan));
                cmd.Parameters.Add(new SqlParameter("@tenBan", tenBan));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool CapNhapBanDirtyread(int maBan, string tenBan, int maKhu, int maLoaiBan, int tinhTrang)
        {
            try
            {
                con.Open();
                string strcmd = "dbo.spDirtyRead_SuaBan_Fixed";
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@maBan", maBan));
                cmd.Parameters.Add(new SqlParameter("@tenBan", tenBan));
                cmd.Parameters.Add(new SqlParameter("@maKhu", maKhu));
                cmd.Parameters.Add(new SqlParameter("@maLoaiBan", maLoaiBan));
                cmd.Parameters.Add(new SqlParameter("@tinhTrang", tinhTrang));
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                DataTable dbtb = new DataTable();
                adapt.Fill(dbtb);
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
